package com.samson.empapp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.samson.empapp.dao.LoginDao;
import com.samson.empapp.util.DbUtil;
import com.samson.empapp.dbo.LoginDbo;

public class LoginDao implements ILoginDao {
	static Logger logger = Logger.getLogger(LoginDao.class);

	@Override
	public LoginDbo getLoginObj(String loginName, String pwd) {
		LoginDbo dbo = null;
		Connection con = DbUtil.getOracleXEConn(); 
		PreparedStatement pstmt = null; 
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("select l.EMPLOYEE_ID empId, role_id as roleId ");
			sb.append("from LOGIN l ");
			sb.append("where l.LOGIN_NAME = ? ");
			sb.append("and l.PWD = ? ");
			pstmt = con.prepareStatement(sb.toString());
			pstmt.setString(1, loginName);
			pstmt.setString(2, pwd);
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				dbo = new LoginDbo();
				dbo.setLoginName(loginName);
				dbo.setPwd(pwd);
				dbo.setRoleId(rs.getInt("roleId"));
				dbo.setEmployeeId(rs.getString("empId"));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return dbo;
	}

	@Override
	public String findEmplId(String loginName, String pwd) {
		String emplId = "";
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("select l.EMPLOYEE_ID empId ");
			sb.append("from LOGIN l ");
			sb.append("where l.LOGIN_NAME = ? ");
			sb.append("and l.PWD = ? ");
			pstmt = con.prepareStatement(sb.toString());
			pstmt.setString(1, loginName);
			pstmt.setString(2, pwd);
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				emplId = rs.getString("empId");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return emplId;
	}

	@Override
	public void insert(LoginDbo dbo) throws Exception {
		 Connection con = DbUtil.getOracleXEConn();
			PreparedStatement pstmt = null;
			StringBuilder sb = null;
			try {
				sb = new StringBuilder();
				sb.append("INSERT INTO LOGIN"); 
				sb.append("(LOGIN_NAME,PWD,ROLE_ID,EMPLOYEE_ID) VALUES "); 
				sb.append("(? ,? ,?,?) ");
				pstmt = con.prepareStatement(sb.toString());
				logger.info("Executing Query " + sb.toString());
				pstmt.setString(1, dbo.getLoginName());
				pstmt.setString(2, dbo.getPwd());
				pstmt.setInt(3, dbo.getRoleId());
				pstmt.setString(4, dbo.getEmployeeId());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("Exception" + e);
				throw e;
			} finally {
				DbUtil.closeDBConnection(pstmt, con, sb);
			}
	}

	@Override
	public void update(LoginDbo dbo) throws Exception {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void delete(int id) {
		// TODO Auto-generated method stub
		
	}

}
